<?php

class Content_Model_DbTable_Categories extends Zend_Db_Table_Abstract {

    protected $_name = 'content_categories';
    private $_digitsPerLevel = 3;

    public function findByUrl($url) {
        return $this->fetchRow(
                        $this->select()->where('url = ?', $url)
        );
    }

    public function getList() {
        $articles = new Content_Model_DbTable_Articles();
        $articlesSelect = $articles->select()
                ->from(array('a' => $articles->info('name')), array(new Zend_Db_Expr('COUNT(*)')))
                ->where('a.category = c.id')
        ;

        $childrenSelect = $this->select()
                ->from(array('c2' => $this->_name), array(new Zend_Db_Expr('COUNT(*)')))
                ->where('c2.parent = c.id')
        ;

        $moveUpSelect = $this->select()
                ->from(array('c3' => $this->_name), array('id'))
                ->where('c3.parent = c.parent')
                ->order('sortorder')
                ->limit(1)
        ;

        $moveDownSelect = $this->select()
                ->from(array('c3' => $this->_name), array('id'))
                ->where('c3.parent = c.parent')
                ->order('sortorder DESC')
                ->limit(1)
        ;

        $select = $this->select()
                ->from(array('c' => $this->_name), array('id', 'title', 'url', 'parent'))
                ->columns(array(
                    'level' => new Zend_Db_Expr("LENGTH(`sortorder`) DIV {$this->_digitsPerLevel}"),
                    'articles_count' => new Zend_Db_Expr('(' . $articlesSelect . ')'),
                    'has_children' => new Zend_Db_Expr('(' . $childrenSelect . ') > 0'),
                    'can_move_up' => new Zend_Db_Expr('(' . $moveUpSelect . ') <> c.id'),
                    'can_move_down' => new Zend_Db_Expr('(' . $moveDownSelect . ') <> c.id'),
                ))
                ->order('sortorder')
        ;

//        Zend_Debug::dump($select->__toString());
//        exit;

        return $this->fetchAll($select);
    }

    public function getShortList() {
        $select = $this->select()
                ->from(array('c' => $this->_name), array('id'))
                ->columns(array(
                    'title' => new Zend_Db_Expr("CONCAT(REPEAT(' ', (LENGTH(`sortorder`) DIV {$this->_digitsPerLevel}) * 2), title)"),
                ))
                ->order('sortorder')
        ;

//        Zend_Debug::dump($select->__toString());
//        exit;

        return $this->fetchAll($select);
    }
    
    public function fetchNewChild($pid) {
        $row = $this->fetchNew();

        // find last brother
        $brother = $this->findLastChild($pid);

        // if no brothers
        if (null === $brother) {
            $parent = $this->find($pid)->current();
            $row->sortorder = $parent->sortorder . str_repeat('0', $this->_digitsPerLevel);
            return $row;
        } else {
            $prefix = substr($brother->sortorder, 0, strlen($brother->sortorder) - $this->_digitsPerLevel);
            $tail = (int) substr($brother->sortorder, strlen($brother->sortorder) - $this->_digitsPerLevel);
            $row->sortorder = $prefix . str_pad(++$tail, $this->_digitsPerLevel, '0', STR_PAD_LEFT);
        }
        return $row;
    }

    public function moveUp($id) {
        $row = $this->find($id)->current();
        $bros = $this->fetchChildIds($row->parent);
        $index = array_search($row->id, $bros);

        if ($index > 0) {
            $prev = $this->find($bros[$index - 1])->current();
            $this->switchSortorder($row, $prev);
            return 1;
        }
        return 'nothing to do';
    }

    public function moveDown($id) {
        $row = $this->find($id)->current();
        $bros = $this->fetchChildIds($row->parent);
        $index = array_search($row->id, $bros);

        if ($index < count($bros) - 1) {
            $next = $this->find($bros[$index + 1])->current();
            $this->switchSortorder($row, $next);
            return 1;
        }
        return 'nothing to do';
    }

    private function findLastChild($pid) {
        $select = $this->select()
                ->where('parent = ?', $pid)
                ->order('sortorder DESC')
        ;
        return $this->fetchRow($select);
    }

    private function refreshSortorder($pid = 1, $parentsortorder = '') {
        $select = $this->select()
                ->where('parent = ?', $pid)
                ->order(array('sortorder', 'id'))
        ;

        $i = 0;
        foreach ($this->fetchAll($select) as $row) {
            $sortorder = $parentsortorder . str_pad($i++, $this->_digitsPerLevel, '0', STR_PAD_LEFT);
            $row->sortorder = $sortorder;
            $row->save();
            $this->refreshSortorder($row->id, $sortorder);
        }
    }

    private function fetchChildIds($pid) {
        $select = $this->select()
                ->from($this->_name, array('id'))
                ->where('parent = ?', $pid)
                ->order('sortorder')
        ;
        return $this->getAdapter()->fetchCol($select);
    }

    private function switchSortorder(Zend_Db_Table_Row $r1, Zend_Db_Table_Row $r2) {
        $tmp = $r1->sortorder;
        $r1->sortorder = $r2->sortorder;
        $r2->sortorder = $tmp;
        $r1->save();
        $r2->save();
        $this->refreshSortorder($r1->id, $r1->sortorder);
        $this->refreshSortorder($r2->id, $r2->sortorder);
    }

}
